package com.eascs.admin.o2o.financial.service;


import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.admin.o2o.financial.dao.O2oFinancialDao;
import com.eascs.admin.o2o.financial.vo.O2oFinancialVo;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;

@Service
public class O2oFinancialService {

	@Autowired
	O2oFinancialDao financialDao;

	/**
	 * @Description:
	 *               <p>
	 *               分页查询_财务查询包含接力贷、分期贷_最新
	 *               </p>
	 * @author LMX
	 * @date 2018年1月5日 上午11:14:06
	 */
	public PageResult<Object[]> findByPage(O2oFinancialVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT * FROM( ");//财务查询_排除接力贷、分期贷
		querySql.append(" SELECT oll.loanapply_no,oll.bill_code,ba.loaner_name ");
		querySql.append(" ,oll.operator_name,ba.loan_bank,CONCAT(oll.ltd_code,'-',oll.ltd_name) AS LongltdName, opr.province_name, oll.loan_type  ");
		querySql.append(" ,ba.loan_amount/10000 AS loan_amount  ");
		querySql.append(" ,DATE_FORMAT(ba.loan_date,'%Y-%m-%d') AS loan_date  ");
		querySql.append(" ,DATE_FORMAT(ADDDATE(ba.loan_date,INTERVAL ba.loan_term  MONTH),'%Y-%m-%d') AS end_bank_date,oll.wf_type  ");
		querySql.append(" ,opr.province_code  ");
		querySql.append(" FROM o2o_loanapply oll  ");
		querySql.append(" LEFT JOIN o2o_bank_loan_data ba ON oll.bill_code = ba.bill_code ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(" WHERE /**DATA_PERMISSION(oll.LTD)**/ 1=1 AND oll.loan_flg='Y' AND ba.is_del='N' AND oll.loan_type NOT IN ('EA','FQ')  ");
		querySql.append(" UNION ");
		querySql.append(" SELECT oll.loanapply_no,oll.bill_code,oeld.loaner_name,oll.operator_name,oeld.loan_bank ");
		querySql.append(" ,CONCAT(oll.ltd_code,'-',oll.ltd_name) AS LongltdName,opr.province_name,oeld.loan_type");
		querySql.append(" ,oeld.loan_amount/10000 AS loan_amount");
		querySql.append(" ,DATE_FORMAT(oeld.loan_date,'%Y-%m-%d') AS loan_date ");
		querySql.append(" ,DATE_FORMAT(ADDDATE(oeld.loan_date,INTERVAL oeld.loan_term  MONTH),'%Y-%m-%d') AS end_bank_date,oll.wf_type ");
		querySql.append(" ,opr.province_code   ");
		querySql.append("  FROM o2o_ea_loan_data oeld  ");
		querySql.append("  LEFT JOIN o2o_loanapply oll on  oll.bill_code=oeld.bill_code_ea  ");
		querySql.append("  LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id  ");
		querySql.append("  LEFT JOIN o2o_ea_loan_settle oels on oeld.id=oels.loan_data_id  ");
		querySql.append("  where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'ZD' AND oll.wf_type NOT IN('T')");//排除接力分期的作废数据
		querySql.append(" ) T WHERE 1=1 ");
		if (!StringUtils.isEmpty(vo.getLoanBillCode())) {
			querySql.append(" and T.bill_code like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanBillCode());
		}
		if (!StringUtils.isEmpty(vo.getLoanUserName())) {
			querySql.append(" and T.loaner_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanUserName());
		}
		if (!StringUtils.isEmpty(vo.getOperatorName())) {
			querySql.append(" and T.operator_name like ? ");
			sqlPageQuery.addLikeParam(vo.getOperatorName());
		}
		if (!StringUtils.isEmpty(vo.getLtdName())) {
			querySql.append(" and T.LongltdName like ? ");
			sqlPageQuery.addLikeParam(vo.getLtdName());
		}
		if (!StringUtils.isEmpty(vo.getLoanBank())) {
			querySql.append(" and T.loan_bank = ? ");
			sqlPageQuery.addParam(vo.getLoanBank());
		}
		if (!StringUtils.isEmpty(vo.getProvinceCode())) {
			querySql.append(" and T.province_code = ? ");
			sqlPageQuery.addParam(vo.getProvinceCode());
		}
		if (!StringUtils.isEmpty(vo.getLoanDateStartTime())) {
			querySql.append(" and T.loan_date >= ? ");
			sqlPageQuery.addParam(vo.getLoanDateStartTime());
		}
		if (!StringUtils.isEmpty(vo.getLoanDateEndTime())) {
			querySql.append(" and T.loan_date <= ? ");
			sqlPageQuery.addParam(vo.getLoanDateEndTime());
		}
		if (!StringUtils.isEmpty(vo.getEndBankDateStartTime())) {
			querySql.append(" and T.end_bank_date >= ? ");
			sqlPageQuery.addParam(vo.getEndBankDateStartTime());
		}
		if (!StringUtils.isEmpty(vo.getEndBankDateEndTime())) {
			querySql.append(" and T.end_bank_date <= ? ");
			sqlPageQuery.addParam(vo.getEndBankDateEndTime());
		}
		if (!StringUtils.isEmpty(vo.getWfType())) {
			querySql.append(" and T.wf_type = ? ");
			sqlPageQuery.addParam(vo.getWfType());
		}
		if (StringUtil.hasText(vo.getLoanType())) {
			querySql.append(" and T.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(vo.getLoanType().contains(",")){
				loanTypeA = vo.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+vo.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		querySql.append(" ORDER BY T.loan_date desc ");
		sqlPageQuery.setSumColumnIndexs(new int[] { 8 });//放款金额统计、数据统计
		sqlPageQuery.setQuerySql(querySql.toString());
		return financialDao.findDataWithPage(sqlPageQuery);
	}
	
	
}
